﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.OleDb;
using DTO;
namespace DAO
{
    public class DAOBangDiem:DataProvider
    {
        public static DataTable LayDSDiem()
        {
            DataTable bangdiem;
            OleDbConnection ketNoi = Connect();
            string chuoilenh = "select * from BangDiemMonHoc";
            DataSet ds = new DataSet();
            OleDbDataAdapter da = new OleDbDataAdapter(chuoilenh, ketNoi);
            da.Fill(ds);
            bangdiem = ds.Tables[0];
            Disconnect();
            return bangdiem;
        }

        public static DTOBangDiem LayDiem(int mshs,string mamon,int malop)
        {            
            OleDbConnection ketNoi = Connect();
            string chuoilenh = "select * from BangDiemMonHoc where mshs=@mshs and mamon=@mamon and malop=@malop";
            DataSet ds = new DataSet();
            OleDbDataAdapter da = new OleDbDataAdapter(chuoilenh, ketNoi);
            da.SelectCommand.Parameters.AddWithValue("@mshs", mshs);
            da.SelectCommand.Parameters.AddWithValue("@mamon", mamon);
            da.SelectCommand.Parameters.AddWithValue("@malop", malop);
            da.Fill(ds);
            var b = new DTOBangDiem();
            if (ds.Tables[0].Rows.Count > 0)
            {
                b.Diem15phut = Convert.ToInt32(ds.Tables[0].Rows[0]["Diem15p"]);
                b.Diem45phut = Convert.ToInt32(ds.Tables[0].Rows[0]["Diem1tiet"]);
                b.DiemCuoiHK = Convert.ToInt32(ds.Tables[0].Rows[0]["DiemCuoiHocKy"]);
            }            
            Disconnect();
            return b;
        }

        public static DataTable LayDSDiem(string hocky,string mamon,int malop)
        {
            DataTable bangdiem;
            OleDbConnection ketNoi = Connect();
            string chuoilenh = "select * from BangDiemMonHoc where hocky=@hocky and mamon=@mamon and malop=@malop";
            DataSet ds = new DataSet();
            OleDbDataAdapter da = new OleDbDataAdapter(chuoilenh, ketNoi);
            da.SelectCommand.Parameters.AddWithValue("@hocky", hocky);
            da.SelectCommand.Parameters.AddWithValue("@mamon", mamon);
            da.SelectCommand.Parameters.AddWithValue("@malop", malop);
            da.Fill(ds);
            bangdiem = ds.Tables[0];
            Disconnect();
            return bangdiem;
        }
        public static DataTable LayDSLop()
        {
            DataTable banglop;
            OleDbConnection ketNoi = Connect();
            string chuoilenh = "select MaLop,TenLop from Lop";
            DataSet ds = new DataSet();
            OleDbDataAdapter da = new OleDbDataAdapter(chuoilenh, ketNoi);
            da.Fill(ds);
            banglop = ds.Tables[0];
            Disconnect();
            return banglop;
        }
        public static DataTable LayDSHSCuaLop(string malop)
        {
            DataTable banghs;
            OleDbConnection ketNoi = Connect();
            string chuoilenh = "select MSHS,HoTen from HocSinh where MaLop=@malop";
            OleDbCommand lenh = new OleDbCommand(chuoilenh, ketNoi);
            DataSet ds = new DataSet();
            OleDbParameter value = new OleDbParameter("@malop", malop);
            OleDbDataAdapter da = new OleDbDataAdapter();
            da.SelectCommand = lenh;
            da.SelectCommand.Parameters.Add(value);
            da.Fill(ds);
            banghs = ds.Tables[0];
            Disconnect();
            return banghs;
        }
        public static int ThemDiem(DTOBangDiem diem)
        {
            OleDbConnection ketnoi = Connect();
            float dtb = (float)Math.Round((diem.Diem15phut + (diem.Diem45phut * 2) + (diem.DiemCuoiHK * 3)) / 6,2);
            string chuoilenh = "insert into BangDiemMonHoc(HocKy,MaLop,MaMon,MSHS,Diem15p,Diem1tiet,DiemCuoiHocKy,Diemtb) values(@hk,@malop,@mamon,@mahs,@diem15,@diem45,@diemcuoihk,Round(@diemtb,2))";
            OleDbCommand lenh = new OleDbCommand(chuoilenh, ketnoi);
            OleDbParameter values = new OleDbParameter("@hk", diem.HocKy);
            lenh.Parameters.Add(values);
            values = new OleDbParameter("@malop", diem.MaLop);
            lenh.Parameters.Add(values);
            values = new OleDbParameter("@mamon",diem.MaMon);
            lenh.Parameters.Add(values);
            values = new OleDbParameter("@mahs", diem.MaHS);
            lenh.Parameters.Add(values);
            values = new OleDbParameter("@diem15", diem.Diem15phut);
            lenh.Parameters.Add(values);
            values = new OleDbParameter("@diem45", diem.Diem45phut);
            lenh.Parameters.Add(values);
            values = new OleDbParameter("@diemcuoihk",diem.DiemCuoiHK);
            lenh.Parameters.Add(values);
            values = new OleDbParameter("@diemtb", dtb);
            lenh.Parameters.Add(values);
            int result = lenh.ExecuteNonQuery();
            Disconnect();
            return result;
        }
        public static int XoaDiem(int MBangDiem)
        {
            OleDbConnection ketnoi = Connect();
            string chuoilenh = "delete from BangDiemMonHoc where MaBangDiemMonHoc = @ma";
            OleDbCommand lenh = new OleDbCommand(chuoilenh, ketnoi);
            OleDbParameter values = new OleDbParameter("@ma", MBangDiem);
            lenh.Parameters.Add(values);
            int result = lenh.ExecuteNonQuery();
            Disconnect();
            return result;
        }
        public static int SuaDiem(DTOBangDiem diemsua)
        {
            OleDbConnection ketnoi = Connect();
            float dtb = (float)Math.Round((diemsua.Diem15phut + (diemsua.Diem45phut * 2) + (diemsua.DiemCuoiHK * 3)) / 6, 2);
            string chuoilenh ="update BangDiemMonHoc set HocKy=@hk,MaLop=@malop,MaMon=@mamon,Diem15p=@diem15,Diem1tiet=@diem45,DiemCuoiHocKy=@diemck,Diemtb=@diemtb where MSHS=@mahs";
            OleDbCommand lenh = new OleDbCommand(chuoilenh, ketnoi);
            OleDbParameter values = new OleDbParameter("@hk", diemsua.HocKy);
            lenh.Parameters.Add(values);
            values = new OleDbParameter("@malop", diemsua.MaLop);
            lenh.Parameters.Add(values);
            values = new OleDbParameter("@mamon", diemsua.MaMon);
            lenh.Parameters.Add(values);
            values = new OleDbParameter("@diem15", diemsua.Diem15phut);
            lenh.Parameters.Add(values);
            values = new OleDbParameter("@diem45", diemsua.Diem45phut);
            lenh.Parameters.Add(values);
            values = new OleDbParameter("@diemck", diemsua.DiemCuoiHK);
            lenh.Parameters.Add(values);
            values = new OleDbParameter("@diemtb", dtb);
            lenh.Parameters.Add(values);
            values = new OleDbParameter("@mahs", diemsua.MaHS);
            lenh.Parameters.Add(values);            
            int result = lenh.ExecuteNonQuery();
            Disconnect();
            return result;
        }
    }
}
